<?php session_start(); ?>
<!DOCTYPE HTML>
<html>
<head>
	<title>Stewie's Market</title>
	<link rel="stylesheet" href="cs2102.css">
</head>

<body>

	<!-- Site navigation menu -->
	<?php include 'nav.php'; 

	function init_db()
	{
		// Open database connection
 		$dbHostPort="1521";
 		$dbHost = "sid3.comp.nus.edu.sg";
		$dbServiceName = "sid3.comp.nus.edu.sg";
		$usr = "A0103863";
		$pswd = "cs2102std";
		$dbConnStr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$dbHost.")(PORT=".$dbHostPort."))(CONNECT_DATA=(SERVICE_NAME=".$dbServiceName.")))";
		$dbConn = oci_connect($usr,$pswd,$dbConnStr);

		if(!$dbConn)
		{
			$err = oci_error();
			print('Could not establish a database connection: ' . $err['message']);
		}
		return $dbConn;
	}

	function create_sql_from_filter($free_search, $category, $request, $sort_by_date)
	{
		$sql = "SELECT a.title, a.category, a.condition, a.description, a.years_of_use, a.request, a.price, s.name, to_char(a.time_created, 'DD MON HH24:MI') , s.matric FROM ad a, student s WHERE a.student=s.matric";

		// If no category or requests are selected, no results can be returned
		if (count($category) == 0 || count($request) == 0){
			return $sql . " AND 1=0 ";
		}

		$sql = $sql . " AND a.category IN (";
		$comma = false;
		foreach($category as $i)
		{
			if ($comma == true) $sql = $sql.",";
			$sql = $sql."'".$i."'";
			$comma = true;
		}
		$sql = $sql . ") ";
		$sql = $sql . " AND a.request IN (";
		$comma = false;
		foreach($request as $i)
		{
			if ($comma == true) $sql = $sql.",";
			$sql = $sql."'".$i."'";
			$comma = true;
		}
		$sql = $sql . ") ";

		if ($free_search)
		{
			$sql = $sql . " AND title LIKE '%" . $free_search . "%' ";
		}

		if ($sort_by_date) $sql = $sql . " ORDER BY a.time_created DESC";
		else $sql = $sql . " ORDER BY a.price ASC";
		return $sql;
	}

	function create_sql_for_empty_search($sort_by_price)
	{
		$sql = "SELECT a.title, a.category, a.condition, a.description, a.years_of_use, a.request, a.price, a.price, to_char(a.time_created, 'DD MON HH24:MI'), a.student FROM ad a";
		
		if ($sort_by_price) $sql = $sql . " ORDER BY a.price ASC";
		else $sql = $sql . " ORDER BY a.time_created DESC";
		return $sql;
	}

	function fetch_results_for_query($db, $query)
	{
		$stmt = oci_parse($db,$query);
		if (!oci_execute($stmt)) {
			$err = oci_error($stmt);
			print('Query failed: '.$err['message']);
		}
		// Create result array with one entry per row
		$res = array();
		while ($next = oci_fetch_row($stmt))
			array_push($res, $next); // append to res array
		return $res;
	}

	function print_results($res)
	{
		if (count($res) > 0){
			echo "<table width='100%'>";
			echo "<col width='50%'>";
			echo "<col width='14%'>";
			echo "<col width='14%'>";
			echo "<col width='22%'>";
			echo "<tr>";
			echo "<td> <b> Item </td>";
			echo "<td> <b> Request </td>";
			echo "<td> <b> Price (SGD) </td>";
			echo "<td> <b> Time Submitted </td> ";
			echo "</tr>";
			foreach ($res as $col) { // for every row
				echo "<tr>";
				echo "<td><a href='detail_page.php?title=".$col[0]."&category=".$col[1]."&condition=".$col[2]."&description=".$col[3]."&years_of_use=".$col[4]."&request=".$col[5]."&price=".$col[6]."&student=".$col[7]."&date=".$col[8]."&matric=".$col[9]."'>".$col[0]."</a></td>";
				echo "<td>".$col[5]."</td>";
				echo "<td>".$col[6]."</td>";
				echo "<td>".$col[8]."</td>";
				echo "</tr>";
			}
			echo "</table>";
		}else{
			print "There are no results to show. Search for something to the left!";
		}
	}
	?>

	<div class="container">
		<!-- Main content -->
		<br /><h1>Search in Stewie's Market</h1>

		<div class="search">
			<b>Search for items in the form below:</b><br><br>
			<div class = "boxed">
				<form action="search.php" method="get">
					Free text search: 
					
				<div class="boxes">
					<input type="text" placeholder="Introduction to Databa..." size=30 value="<?php if(isset($_GET['free_search'])) echo $_GET['free_search']; ?>" name="free_search">
				</div>
			</div>
				<div class = "boxed">
					<img src="SearchHeader1.png" alt="I want to">
					<!-- When want to 'Buy', display ads with request 'Sell' and vice versa -->
					<div class="boxes">
						<input type="checkbox" name="request[0]" <?php if(isset($_GET['request'][0]) && $_GET['request'][0] == 'Sell') { print ' checked '; } ?> value="Sell" /> Buy<br> 
						<input type="checkbox" name="request[1]" <?php if(isset($_GET['request'][1]) && $_GET['request'][1] == 'Buy') { print ' checked '; } ?> value="Buy" /> Sell<br>
						<input type="checkbox" name="request[2]" <?php if(isset($_GET['request'][2]) && $_GET['request'][2] == 'Trade') { print ' checked '; } ?> value="Trade" /> Trade<br>
					</div>
				</div>
				<div class = "boxed">
					<img src="SearchHeader2.png" alt="I want to">
					<div class="boxes">
						<input type="checkbox" name="category[0]" <?php if(isset($_GET['category'][0]) &&$_GET['category'][0] == 'Clothes') { print ' checked '; } ?> value="Clothes" /> Clothes<br>
						<input type="checkbox" name="category[1]" <?php if(isset($_GET['category'][1]) &&$_GET['category'][1] == 'Electronics') { print ' checked '; } ?> value="Electronics" /> Electronics<br>
						<input type="checkbox" name="category[2]" <?php if(isset($_GET['category'][2]) &&$_GET['category'][2] == 'Furniture') { print ' checked '; } ?> value="Furniture" /> Furniture<br>
						<input type="checkbox" name="category[3]" <?php if(isset($_GET['category'][3]) &&$_GET['category'][3] == 'Stationary') { print ' checked '; } ?> value="Stationary" /> Stationary<br>
						<input type="checkbox" name="category[4]" <?php if(isset($_GET['category'][4]) &&$_GET['category'][4] == 'Kitchenware') { print ' checked '; } ?> value="Kitchenware" /> Kitchenware<br>
						<input type="checkbox" name="category[5]" <?php if(isset($_GET['category'][5]) &&$_GET['category'][5] == 'Books') { print ' checked '; } ?> value="Books" /> Books<br>
						<input type="checkbox" name="category[6]" <?php if(isset($_GET['category'][6]) &&$_GET['category'][6] == 'Others') { print ' checked '; } ?> value="Others" /> Others<br>
					</div>
				</div>
				<div class = "boxed">
					<img src="SearchHeader3.png" alt="I want to">
					<div class="boxes">
						<input type="radio" name="filter_by" <?php if(isset($_GET['filter_by']) && $_GET['filter_by'] == 'price') { print ' checked '; } ?> value="price" />Price<br>
						<input type="radio" name="filter_by" <?php if(!isset($_GET['filter_by']) || $_GET['filter_by'] != 'price') { print ' checked '; } ?> value="date_added" />Date added<br>
					</div><br>
				</div>
				<input type="submit" value="Search">
			</form>
		</div>

		<div class="result">
			<b>Results:</b><br><br>
			<?php
			if(isset($_GET['free_search']) && isset($_GET['category']) && isset($_GET['request'])){
				$db = init_db();
				$sql = create_sql_from_filter($_GET['free_search'], $_GET['category'], $_GET['request'], ($_GET['filter_by'] == 'date_added'));
				$res = fetch_results_for_query($db, $sql);
				print_results($res);
			}
			else if ((!isset($_GET['free_search']) || $_GET['free_search'] == null) &&!isset($_GET['category']) && !isset($_GET['request'])){
				$db = init_db();
				$sql = create_sql_for_empty_search(($_GET['filter_by'] == 'price'));
				// $sql = "SELECT a.title, a.category, a.condition, a.description, a.years_of_use, a.request, a.price, a.price, to_char(a.time_created, 'DD MON HH24:MI') FROM ad a";
				$res = fetch_results_for_query($db, $sql);
				print_results($res);	
			}
			else if(!isset($_GET['category']) && !isset($_GET['request'])) echo "Invalid search, add category and request";
			else if(!isset($_GET['category'])) echo "Invalid search, add a category";
			else if(!isset($_GET['request'])) echo "Invalid search, add a request";
			?>
		</div>

		<!-- Sign and date the page, it's only polite! -->
		<address>Made by Project group 31 for CS2102, NUS 2013.</address>
	</div>

</body>
</html>